﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Exam.Core.DBUtility;
using System.Data.OleDb;

namespace Exam.Core.DAL {
    public class Department {
        //返回所有
        public List<Exam.Core.Model.Department> getDepartmentAllList(string departmentid) {
            List<Exam.Core.Model.Department> list = new List<Exam.Core.Model.Department>();
            string SQL = "SELECT * FROM Department WHERE DepartmentID IN (" + departmentid.Trim(',') + " )";
            DataTable dt = DbHelperOleDb.Query(SQL).Tables[0];
            foreach (DataRow dr in dt.Rows) {
                Exam.Core.Model.Department obj = new Exam.Core.Model.Department();
                obj.DepartmentID = dr["DepartmentID"].ToString().Trim();
                obj.DepartmentName = dr["DepartmentName"].ToString().Trim();
                list.Add(obj);
            }
            //可以加入缓存
            return list;

        }
        //返回所有部门
        public List<Exam.Core.Model.Department> getDepartmentAllList() {
            List<Exam.Core.Model.Department> list = new List<Exam.Core.Model.Department>();
            string SQL = "SELECT * FROM Department ORDER BY DepartmentID DESC";
            DataTable dt = DbHelperOleDb.Query(SQL).Tables[0];
            foreach (DataRow dr in dt.Rows) {
                Exam.Core.Model.Department obj = new Exam.Core.Model.Department();
                obj.DepartmentID = dr["DepartmentID"].ToString().Trim();
                obj.DepartmentName = dr["DepartmentName"].ToString().Trim();
                list.Add(obj);
            }
            //可以加入缓存
            return list;

        }

        public bool Add(string deptname) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO Department(");
            strSql.Append("DepartmentName)");
            strSql.Append(" VALUES (");
            strSql.Append("@DepartmentName)");
            OleDbParameter[] parameters = {
					new OleDbParameter("@DepartmentName", OleDbType.VarChar,200)};
            parameters[0].Value = deptname;
            bool result = false;
            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0) {
                result = true;
            }
            return result;

        }

        /// <summary>
        /// 添加并返回ID
        /// </summary>
        /// <param name="deptname"></param>
        /// <returns></returns>
        public int AddReturnId(string deptname) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO Department(");
            strSql.Append("DepartmentName)");
            strSql.Append(" VALUES (");
            strSql.Append("@DepartmentName)");
            OleDbParameter[] parameters = {
					new OleDbParameter("@DepartmentName", OleDbType.VarChar,200)};
            parameters[0].Value = deptname;
            int result = 0;
            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0) {
                string SQL = "SELECT TOP 1 DepartmentID FROM Department WHERE DepartmentName=@DepartmentName ORDER BY DepartmentID DESC";
                OleDbParameter[] paras = {
					new OleDbParameter("@DepartmentName", OleDbType.VarChar,200)};
                paras[0].Value = deptname;
                object obj = DbHelperOleDb.GetSingle(SQL, paras);
                if (obj == null) {
                    result = 0;
                }
                else {
                    result = int.Parse(obj.ToString());
                }
            }
            return result;
        }

        public bool Edit(int _id, string _name) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE Department SET ");
            strSql.Append("DepartmentName=@DepartmentName");
            strSql.Append(" WHERE DepartmentID=@DepartmentID ");
            OleDbParameter[] parameters = {
					new OleDbParameter("@DepartmentName", OleDbType.VarChar,200),
                    new OleDbParameter("@DepartmentID", OleDbType.Integer,4)
                };
            parameters[0].Value = _name.Trim();
            parameters[1].Value = _id;
            bool result = false;
            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0) {
                result = true;
            }
            return result;
        }

        //删除
        public bool Del(int id) {
            bool result = false;
            string SQL = "DELETE FROM Department WHERE DepartmentID =" + id + "";
            try {
                DbHelperOleDb.ExecuteSql(SQL);
                result = true;
            }
            catch {
                result = false;
            }
            return result;
        }



        
    }
}
